using System;
using System.Data;

using System.Configuration;

using System.Data.OleDb;
using Entity;

namespace SalesVMT
{
    public class InventoryDB
    {
        #region PUBLIC
        
        public static DataSet GetList(out int ItemCount, string keyWord, int currentPage, int pageSize)
        {

            string criteria;
            criteria = " Where 0 = 0 ";
            if (keyWord.Trim().Length > 0)
            {
                criteria = criteria + " And (Item_Code Like N'%" + keyWord + "%' "+
                                        " OR Item_Name Like N'%" + keyWord + "%')";
            }

            string sql = "";
            DataSet ds = new DataSet();

            sql = "SELECT Count(Item_ID) FROM vwInventory " + criteria;
            ds = clsConnection.Query(sql);
            ItemCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

            int StartIndex = currentPage * pageSize + 1;
            int EndIndex = StartIndex + pageSize - 1;

            string OrderBy = "Item_Name";
            string OrderDirection = "DESC";

            sql = " WITH tmpnventory AS" +
                            " ( SELECT	vwInventory.*," +
                            "   ROW_NUMBER() OVER (ORDER BY " + OrderBy + " " + OrderDirection + ") AS RowNumber" +
                            "   FROM vwInventory " + criteria + ")" +
                            " SELECT * FROM tmpnventory" +
                            " WHERE	RowNumber BETWEEN " + StartIndex.ToString() + " AND " + EndIndex.ToString() +
                            " ORDER BY RowNumber ASC";
            ds = clsConnection.Query(sql);

            return ds;

        }
        public static DataSet GetQuantityByItem(int ItemID)
        {
            DataSet retVal = null;
            OleDbConnection dbConn = clsConnection.CreateConnection();

            OleDbCommand dbCmd = new OleDbCommand("SalesVMT_Inventory_GetQuantityByItem", dbConn);
            dbCmd.Parameters.AddWithValue("@ItemID", ItemID);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
       #endregion
    }
}
